﻿<%@ Page Title="View Grades" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ViewGrades.aspx.cs" Inherits="Testify.ViewGrades" %>
<%@ MasterType VirtualPath="~/Site.Master" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="container">
    <asp:GridView ID="grdGrades" runat="server" DataSourceID="dsGrades" AutoGenerateColumns="False" EmptyDataText="None of your tests have been completely graded." >
        <Columns>
            <asp:BoundField DataField="section_name" HeaderText="Class" >
                <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="test_title" HeaderText="Test" >
                <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="score" HeaderText="Grade" >
                <ItemStyle HorizontalAlign="Right" />
            </asp:BoundField>
            <asp:TemplateField HeaderText="Responses &amp; Answers">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkView" runat="server" Text="View" onclick="lnkView_Click" CommandArgument='<%# Bind("test_id") %>' CommandName='<%# Bind("assignment_id") %>' />
                </ItemTemplate>
                <HeaderStyle Wrap="False" />
                <ItemStyle HorizontalAlign="Center" />
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="dsGrades" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="
SELECT assignment_id, test_id,
       course_type_prefix || ' ' || course_number || '-' || section_number || ': ' || course_name AS section_name,
       test_type_name || ': ' || test_name AS test_title,
       TO_CHAR(100 * NVL(CASE WHEN total_credit &lt; 0 THEN NULL ELSE total_credit / total_weight END, 0), 'FM990.00') || '%' AS score
  FROM (SELECT a.assignment_start, a.assignment_end, a.assignment_id, a.assignment_limit,
               t.test_id, t.course_id, t.teacher_id, t.test_name,
               tt.test_type_id, tt.test_type_name,
               s.section_id, s.section_number, s.teacher_id,
               cls.class_id, cls.term_id,
               crs.course_id, crs.course_name, crs.course_number,
               ct.course_type_id, ct.course_type_prefix, ct.course_type_name,
               SUM(r.response_credit) AS total_credit,
               (SELECT SUM(q.question_weight)
                  FROM segment seg
                       JOIN question q ON (q.segment_id = seg.segment_id)
                 WHERE seg.test_id = t.test_id
                   AND seg.segment_is_bonus != 'Y'
                   AND 'D' NOT IN (seg.segment_maint, q.question_maint)) AS total_weight
          FROM response r
               JOIN assignment a ON (a.assignment_id = r.assignment_id)
                    JOIN test t ON (t.test_id = a.test_id)
                         JOIN test_type tt ON (tt.test_type_id = t.test_type_id)
                    JOIN section s ON (s.section_id = a.section_id)
                         JOIN class cls ON (cls.class_id = s.class_id)
                              JOIN course crs ON (crs.course_id = cls.course_id)
                                   JOIN course_type ct ON (ct.course_type_id = crs.course_type_id)
         WHERE r.student_id = :p_StudentID
           AND cls.term_id = (SELECT current_term_id FROM environment)
           AND sysdate &gt;= a.assignment_end
           AND NOT EXISTS (SELECT 1
                             FROM response rr
                            WHERE rr.student_id    = :p_StudentID
                              AND rr.assignment_id = a.assignment_id
                              AND rr.response_credit IS NULL)
           AND 'D' NOT IN (tt.test_type_maint, s.section_maint, cls.class_maint, crs.course_maint, ct.course_type_maint)
         GROUP BY a.assignment_start, a.assignment_end, a.assignment_id, a.assignment_limit,
                  t.test_id, t.course_id, t.teacher_id, t.test_name, t.test_type_id, t.test_maint,
                  tt.test_type_id, tt.test_type_name, tt.test_type_maint,
                  s.section_id, s.section_number, s.class_id, s.teacher_id, s.section_maint,
                  cls.class_id, cls.course_id, cls.term_id, cls.class_maint,
                  crs.course_id, crs.course_name, crs.course_type_id, crs.course_number, crs.course_maint,
                  ct.course_type_id, ct.course_type_prefix, ct.course_type_name, ct.course_type_maint)
 ORDER BY course_type_prefix, course_number, section_number,
          test_type_name, test_name" >
        <SelectParameters>
            <asp:SessionParameter Name="p_StudentID" SessionField="UserID" />
        </SelectParameters>
    </asp:SqlDataSource>
</div>
</asp:Content>